package com.mgy.common.excel;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.xml.parsers.ParserConfigurationException;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 事件模式读取excel，支持excel2007及以上版本
 *
 * @author maguoyong
 * @since 2018/6/13
 */
public class ExcelReader {
    private final OPCPackage opcPackage;

    /**
     * 工作簿解析
     */
    private class SheetParse implements SheetContentsHandler {
        public DataTable getDataTable() {
            return dataTable;
        }

        /**
         * 工作簿
         */
        private DataTable dataTable;
        /**
         * 工作簿的列头映射 A,B,C,D……
         */
        private List<String> columns = new ArrayList<>();

        private Map<String, String> columnMap = new HashMap<>();

        /**
         * 工作簿索引
         */
        private int sheetIndex = -1;
        /**
         * 工作簿名称
         */
        private String sheetName = null;

        private IRowReader rowReader;

        private List<String> currentRow = new ArrayList<>();

        SheetParse(int sheetIndex, String sheetName) {
            this.sheetIndex = sheetIndex;
            this.sheetName = sheetName;
            this.dataTable = new DataTable(sheetName);
        }

        SheetParse(int sheetIndex, String sheetName, IRowReader rowReader) {
            this(sheetIndex, sheetName);
            this.rowReader = rowReader;
        }

        @Override
        public void startRow(int rowNum) {
            this.columnMap.clear();
        }

        @Override
        public void endRow(int rowNum) {
            if (rowNum == 0) {
                for (String key : columnMap.keySet()) {
                    try {
                        if (rowReader == null) {
                            this.dataTable.addColumn(columnMap.get(key), String.class);
                        }
                        columns.add(key);
                        currentRow.add(columnMap.get(key));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            } else {
                if (columnMap.size() == 0) {
                    return;
                }
                for (int i = 0; i < columns.size(); i++) {
                    String value = columnMap.get(columns.get(i));
                    currentRow.add(value == null ? "" : value);
                }
                if (rowReader == null) {
                    this.dataTable.addRow(currentRow.toArray());
                }
            }
            if (rowReader != null) {
                rowReader.rowRead(sheetIndex, sheetName, rowNum, currentRow);
            }
            currentRow.clear();
        }

        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            String columnName = cellReference.replaceAll("\\d+", "");
            this.columnMap.put(columnName, formattedValue);
        }

        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {

        }


    }


    private ExcelReader(OPCPackage opcPackage) {
        this.opcPackage = opcPackage;
    }


    /**
     * 解析工作簿
     *
     * @param styles                     styles
     * @param readOnlySharedStringsTable readOnlySharedStringsTable
     * @param sheetHandler               sheetHandler
     * @param sheetInputStream           sheetInputStream
     */
    private void readSheet(StylesTable styles, ReadOnlySharedStringsTable readOnlySharedStringsTable, SheetContentsHandler sheetHandler, InputStream sheetInputStream)
            throws IOException, SAXException, ParserConfigurationException {
        DataFormatter formatter = new DataFormatter();
        InputSource sheetSource = new InputSource(sheetInputStream);
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, readOnlySharedStringsTable, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    }

    /**
     * 解析指定sheet名称的工作簿
     *
     * @param sheetName 工作簿名称
     */
    private DataTable readSheet(String sheetName) throws IOException, SAXException, OpenXML4JException, ParserConfigurationException {
        if (sheetName == null || sheetName.trim().length() == 0) {
            throw new IllegalArgumentException("sheetName不能为空");
        }
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(this.opcPackage);
        XSSFReader xssfReader = new XSSFReader(this.opcPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        int sheetIndex = -1;
        InputStream stream = null;
        String currentSheetName = null;
        while (sheetIterator.hasNext()) {
            stream = sheetIterator.next();
            sheetIndex++;
            //工作簿名称
            if (sheetName.equals(sheetIterator.getSheetName())) {
                currentSheetName = sheetIterator.getSheetName();
                break;
            }
        }
        if (currentSheetName == null) {
            throw new IllegalArgumentException("找不到对应的sheet,sheetName=" + sheetName);
        }
        SheetParse sheetParse = new SheetParse(sheetIndex, sheetName);
        this.readSheet(styles, readOnlySharedStringsTable, sheetParse, stream);
        stream.close();
        return sheetParse.getDataTable();
    }

    /**
     * 解析指定sheet名称的工作簿
     *
     * @param sheetName 工作簿名称
     * @param rowReader 回调
     */
    private void readSheet(String sheetName, IRowReader rowReader) throws IOException, SAXException, OpenXML4JException, ParserConfigurationException {
        if (sheetName == null || sheetName.trim().length() == 0) {
            throw new IllegalArgumentException("sheetName不能为空");
        }
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(this.opcPackage);
        XSSFReader xssfReader = new XSSFReader(this.opcPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        int sheetIndex = -1;
        InputStream stream = null;
        String currentSheetName = null;
        while (sheetIterator.hasNext()) {
            stream = sheetIterator.next();
            sheetIndex++;
            //工作簿名称
            if (sheetName.equals(sheetIterator.getSheetName())) {
                currentSheetName = sheetIterator.getSheetName();
                break;
            }
        }
        if (currentSheetName == null) {
            throw new IllegalArgumentException("找不到对应的sheet,sheetName=" + sheetName);
        }
        SheetParse sheetParse = new SheetParse(sheetIndex, sheetName, rowReader);
        this.readSheet(styles, readOnlySharedStringsTable, sheetParse, stream);
        stream.close();
    }

    /**
     * 解析指定sheet索引的工作簿
     *
     * @param sheetIndex 工作簿名称
     */
    private DataTable readSheet(int sheetIndex) throws IOException, SAXException, OpenXML4JException, ParserConfigurationException {
        if (sheetIndex < 0) {
            throw new IllegalArgumentException("sheetIndex必须大于-1");
        }
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(this.opcPackage);
        XSSFReader xssfReader = new XSSFReader(this.opcPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        int currentSheetIndex = -1;
        InputStream stream = null;
        String currentSheetName = null;
        while (sheetIterator.hasNext()) {
            stream = sheetIterator.next();
            currentSheetIndex++;
            //工作簿名称
            if (currentSheetIndex == sheetIndex) {
                currentSheetName = sheetIterator.getSheetName();
                break;
            }
        }
        if (currentSheetName == null) {
            throw new IllegalArgumentException("找不到对应的sheet,sheetIndex=" + sheetIndex);
        }
        SheetParse sheetParse = new SheetParse(sheetIndex, currentSheetName);
        this.readSheet(styles, readOnlySharedStringsTable, sheetParse, stream);
        stream.close();
        return sheetParse.getDataTable();
    }

    /**
     * 解析指定sheet索引的工作簿
     *
     * @param sheetIndex 工作簿名称
     * @param rowReader  回调
     */
    private void readSheet(int sheetIndex, IRowReader rowReader) throws IOException, SAXException, OpenXML4JException, ParserConfigurationException {
        if (sheetIndex < 0) {
            throw new IllegalArgumentException("sheetIndex必须大于-1");
        }
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(this.opcPackage);
        XSSFReader xssfReader = new XSSFReader(this.opcPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        int currentSheetIndex = -1;
        InputStream stream = null;
        String currentSheetName = null;
        while (sheetIterator.hasNext()) {
            stream = sheetIterator.next();
            currentSheetIndex++;
            //工作簿名称
            if (currentSheetIndex == sheetIndex) {
                currentSheetName = sheetIterator.getSheetName();
                break;
            }
        }
        if (currentSheetName == null) {
            throw new IllegalArgumentException("找不到对应的sheet,sheetIndex=" + sheetIndex);
        }
        SheetParse sheetParse = new SheetParse(sheetIndex, currentSheetName, rowReader);
        this.readSheet(styles, readOnlySharedStringsTable, sheetParse, stream);
        stream.close();
    }

    /**
     * 读取第一个工作簿
     */
    private DataTable readFirstSheet() throws Exception {
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(this.opcPackage);
        XSSFReader xssfReader = new XSSFReader(this.opcPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

        if (!sheetIterator.hasNext()) {
            throw new Exception("没有发现sheet");
        }
        InputStream stream = sheetIterator.next();
        //工作簿名称
        String sheetName = sheetIterator.getSheetName();
        SheetParse sheetParse = new SheetParse(0, sheetName);
        this.readSheet(styles, readOnlySharedStringsTable, sheetParse, stream);
        stream.close();
        return sheetParse.getDataTable();
    }

    /**
     * 读取第一个工作簿
     */
    private void readFirstSheet(IRowReader rowReader) throws Exception {
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(this.opcPackage);
        XSSFReader xssfReader = new XSSFReader(this.opcPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

        if (!sheetIterator.hasNext()) {
            throw new Exception("没有发现sheet");
        }
        InputStream stream = sheetIterator.next();
        //工作簿名称
        String sheetName = sheetIterator.getSheetName();
        SheetParse sheetParse = new SheetParse(0, sheetName, rowReader);
        this.readSheet(styles, readOnlySharedStringsTable, sheetParse, stream);
        stream.close();
    }

    /**
     * 读取excel中所有sheet
     *
     * @param rowReader rowReader
     * @throws Exception Exception
     */
    private void readAllSheet(IRowReader rowReader) throws Exception {
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(this.opcPackage);
        XSSFReader xssfReader = new XSSFReader(this.opcPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        int sheetIndex = 0;
        while (sheetIterator.hasNext()) {
            InputStream stream = sheetIterator.next();
            //工作簿名称
            String sheetName = sheetIterator.getSheetName();
            SheetParse sheetParse = new SheetParse(sheetIndex, sheetName, rowReader);
            this.readSheet(styles, readOnlySharedStringsTable, sheetParse, stream);
            stream.close();
            sheetIndex++;
        }
    }

    /**
     * Initiates the processing of the XLS workbook file to CSV.
     *
     * @throws IOException                  IOException
     * @throws OpenXML4JException           OpenXML4JException
     * @throws ParserConfigurationException ParserConfigurationException
     * @throws SAXException                 SAXException
     */
    private List<DataTable> readAllSheet() throws IOException, OpenXML4JException, SAXException, ParserConfigurationException {
        ReadOnlySharedStringsTable readOnlySharedStringsTable = new ReadOnlySharedStringsTable(this.opcPackage);
        XSSFReader xssfReader = new XSSFReader(this.opcPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        List<DataTable> sheets = new ArrayList<>();
        int sheetIndex = 0;
        while (sheetIterator.hasNext()) {
            InputStream stream = sheetIterator.next();
            //工作簿名称
            String sheetName = sheetIterator.getSheetName();
            SheetParse sheetParse = new SheetParse(sheetIndex, sheetName);
            this.readSheet(styles, readOnlySharedStringsTable, sheetParse, stream);
            stream.close();
            sheets.add(sheetParse.getDataTable());
            sheetIndex++;
        }
        return sheets;
    }

    /**
     * 读取excel所有sheet
     *
     * @param excelPath excel的绝对路径
     */
    public static List<DataTable> readAllSheet(String excelPath) throws Exception {
        File file = new File(excelPath);
        return readAllSheet(file);
    }

    /**
     * 读取excel文件所有的sheet
     *
     * @param file excel文件
     */
    public static List<DataTable> readAllSheet(File file) throws Exception {
        if (!file.exists() || !file.isFile()) {
            throw new Exception("文件不存在");
        }
        try (OPCPackage opcPackage = OPCPackage.open(file.getPath(), PackageAccess.READ)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            return excelReader.readAllSheet();
        }
    }

    /**
     * 读取excel文件所有的sheet
     *
     * @param inputStream 流
     */
    public static List<DataTable> readAllSheet(InputStream inputStream) throws Exception {
        if (inputStream == null) {
            throw new Exception("inputStream不能为空");
        }
        try (OPCPackage opcPackage = OPCPackage.open(inputStream)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            return excelReader.readAllSheet();
        }
    }

    /**
     * 读取excel第一个sheet
     *
     * @param excelPath excel绝对路径
     */
    public static DataTable readFirstSheet(String excelPath) throws Exception {
        File file = new File(excelPath);
        return readFirstSheet(file);
    }

    /**
     * 读取excel第一个sheet
     *
     * @param file excel文件
     */
    public static DataTable readFirstSheet(File file) throws Exception {
        if (!file.exists() || !file.isFile()) {
            throw new Exception("文件不存在");
        }
        try (OPCPackage opcPackage = OPCPackage.open(file.getPath(), PackageAccess.READ)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            return excelReader.readFirstSheet();
        }
    }

    /**
     * 读取指定sheetName的工作簿
     *
     * @param inputStream excel
     * @param sheetName   工作簿名称
     */
    public static DataTable readSheet(InputStream inputStream, String sheetName) throws Exception {
        if (inputStream == null) {
            throw new IllegalArgumentException("inputStream不能为空");
        }
        try (OPCPackage opcPackage = OPCPackage.open(inputStream)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            return excelReader.readSheet(sheetName);
        }
    }

    /**
     * 读取指定sheetName的工作簿
     *
     * @param inputStream excel
     * @param sheetName   工作簿名称
     * @param rowReader   回调行
     */
    public static void readSheet(InputStream inputStream, String sheetName, IRowReader rowReader) throws Exception {
        if (inputStream == null) {
            throw new IllegalArgumentException("inputStream不能为空");
        }
        try (OPCPackage opcPackage = OPCPackage.open(inputStream)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            excelReader.readSheet(sheetName, rowReader);
        }
    }


    /**
     * 读取指定sheetName的工作簿
     *
     * @param inputStream excel
     * @param sheetIndex  工作簿索引
     */
    public static DataTable readSheet(InputStream inputStream, int sheetIndex) throws Exception {
        if (inputStream == null) {
            throw new IllegalArgumentException("inputStream不能为空");
        }
        try (OPCPackage opcPackage = OPCPackage.open(inputStream)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            return excelReader.readSheet(sheetIndex);
        }
    }

    /**
     * 读取指定sheetName的工作簿
     *
     * @param inputStream excel
     * @param sheetIndex  工作簿索引
     * @param rowReader   回调
     */
    public static void readSheet(InputStream inputStream, int sheetIndex, IRowReader rowReader) throws Exception {
        if (inputStream == null) {
            throw new IllegalArgumentException("inputStream不能为空");
        }
        try (OPCPackage opcPackage = OPCPackage.open(inputStream)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            excelReader.readSheet(sheetIndex, rowReader);
        }
    }

    /**
     * 读取excel第一个sheet
     *
     * @param inputStream 流
     */
    public static DataTable readFirstSheet(InputStream inputStream) throws Exception {
        if (inputStream == null) {
            throw new Exception("inputStream不能为空");
        }
        try (OPCPackage opcPackage = OPCPackage.open(inputStream)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            return excelReader.readFirstSheet();
        }
    }

    public static void readFirstSheet(InputStream inputStream, IRowReader rowReader) throws Exception {
        if (inputStream == null) {
            throw new Exception("inputStream不能为空");
        }
        try (OPCPackage opcPackage = OPCPackage.open(inputStream)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            excelReader.readFirstSheet(rowReader);
        }
    }

    public static void readAllSheet(InputStream inputStream, IRowReader rowReader) throws Exception {
        if (inputStream == null) {
            throw new Exception("inputStream不能为空");
        }
        try (OPCPackage opcPackage = OPCPackage.open(inputStream)) {
            ExcelReader excelReader = new ExcelReader(opcPackage);
            excelReader.readAllSheet(rowReader);
        }
    }
}
